package com.pan.test.mappers;

import com.pan.test.entity.po.FileInfo;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;
import java.util.Map;

/**
 * 文件信息 数据库操作接口
 */
public interface FileInfoMapper<T, P> extends BaseMapper<T, P> {

    /**
     * 根据FileIdAndUserId更新
     */
    Integer updateByFileIdAndUserId(@Param("bean") T t, @Param("fileId") String fileId, @Param("userId") String userId);


    /**
     * 根据FileIdAndUserId删除
     */
    Integer deleteByFileIdAndUserId(@Param("fileId") String fileId, @Param("userId") String userId);


    /**
     * 根据FileIdAndUserId获取对象
     */
    T selectByFileIdAndUserId(@Param("fileId") String fileId, @Param("userId") String userId);


    void updateFileStatusWithOldStatus(@Param("fileId") String fileId, @Param("userId") String userId, @Param("bean") T t,
                                       @Param("oldStatus") Integer oldStatus);

    void updateFileDelFlagBatch(@Param("bean") FileInfo fileInfo,
                                @Param("userId") String userId,
                                @Param("filePidList") List<String> filePidList,
                                @Param("fileIdList") List<String> fileIdList,
                                @Param("oldDelFlag") Integer oldDelFlag);


    void delFileBatch(@Param("userId") String userId,
                      @Param("filePidList") List<String> filePidList,
                      @Param("fileIdList") List<String> fileIdList,
                      @Param("oldDelFlag") Integer oldDelFlag);

    Long selectUseSpace(@Param("userId") String userId);

    void deleteFileByUserId(@Param("userId") String userId);

    /**
     * 查询总文件大小
     */
    @Select("SELECT COALESCE(SUM(file_size), 0) FROM file_info WHERE del_flag = 2")
    Long selectTotalFileSize();

    /**
     * 查询文件类型统计
     */
    @Select("SELECT file_type as fileType, COUNT(*) as count FROM file_info WHERE del_flag = 2 GROUP BY file_type ORDER BY count DESC")
    List<Map<String, Object>> selectFileTypeStats();

    /**
     * 查询存储使用趋势
     */
    @Select("WITH RECURSIVE date_sequence AS (" +
            "SELECT a.Date " +
            "FROM (" +
            "   SELECT CURDATE() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date " +
            "   FROM (SELECT 0 as a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) a " +
            "   CROSS JOIN (SELECT 0 as a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) b " +
            "   CROSS JOIN (SELECT 0 as a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) c " +
            ") a " +
            "WHERE a.Date BETWEEN #{startDate} AND #{endDate}" +
            "), " +
            "daily_stats AS (" +
            "   SELECT " +
            "       DATE(create_time) as date, " +
            "       SUM(file_size) as storage " +
            "   FROM file_info " +
            "   WHERE DATE(create_time) BETWEEN #{startDate} AND #{endDate} " +
            "       AND del_flag = 2 " +
            "   GROUP BY DATE(create_time)" +
            ") " +
            "SELECT " +
            "   DATE_FORMAT(date_sequence.Date, '%Y-%m-%d') as date, " +
            "   COALESCE(daily_stats.storage, 0) as storage " +
            "FROM date_sequence " +
            "LEFT JOIN daily_stats ON date_sequence.Date = daily_stats.date " +
            "ORDER BY date_sequence.Date ASC")
    List<Map<String, Object>> selectStorageTrend(@Param("startDate") String startDate, @Param("endDate") String endDate);

    /**
     * 搜索文件
     */
    @Select("SELECT f.*, u.nick_name as nickName " +
            "FROM file_info f " +
            "LEFT JOIN user_info u ON f.user_id = u.user_id " +
            "WHERE f.del_flag = 2 " +
            "AND (f.file_name LIKE CONCAT('%', #{keyword}, '%') " +
            "OR f.file_type LIKE CONCAT('%', #{keyword}, '%')) " +
            "ORDER BY f.create_time DESC")
    List<Map<String, Object>> searchFiles(@Param("keyword") String keyword);

    /**
     * 批量删除文件
     */
    @Update("UPDATE file_info SET del_flag = 0 " +
            "WHERE file_id IN " +
            "<foreach collection='fileIds' item='fileId' open='(' separator=',' close=')'>" +
            "#{fileId}" +
            "</foreach>")
    int batchDeleteFiles(@Param("fileIds") List<String> fileIds);

    /**
     * 获取文件下载信息
     */
    @Select("SELECT file_path, file_name, file_size " +
            "FROM file_info " +
            "WHERE file_id = #{fileId} AND del_flag = 2")
    Map<String, Object> getFileDownloadInfo(@Param("fileId") String fileId);
}
